const Router = require('koa-router');
const router = new Router();
const {db, QueryTypes, } = require('../../models/db')
const tool = require('../../tools/tool')
const biz = require('../../tools/biz')
const {sql} = require("@sequelize/core");

// 拜访统计
router.post('/report/bfTj', async ctx => {
    let tid = ctx.user.tenantId;
    let formData = ctx.request.body.formData;
    let currentPage = ctx.request.body.currentPage;
    let pageSize = ctx.request.body.pageSize;
    let offset = (currentPage - 1) * pageSize;
    let beg = formData.beg
    let end = formData.end
    let deptId = formData.deptId
    let buName = formData.buName
    // let areaId = formData.areaId
    let salesId = formData.salesId
    let billDate = []
    if(beg) {
        billDate.push(`billDate >= '${beg}'`)
    }
    if(end) {
        billDate.push(`billDate <= '${end}'`)
    }
    let billDateStr = billDate.join(' and ')
    if(billDateStr) {
        billDateStr = ' and ' + billDateStr
    }
    let sql = `select * from contact_bf where tenantId=${tid} and state='已完成' ${billDateStr} `
    if(salesId) {
        sql += ` and salesId=${salesId} `
    } else {
        if(deptId) {
            let dept = await db.models['dept'].findOne({
                where: {
                    id: deptId
                },
                attributes: ['code']
            })
            let userIds = ` select a.id from admin a inner join (select id from dept where code like '${dept.code}%') b on a.deptId=b.id `
            sql = ` select a.* from (${sql}) a inner join (${userIds}) b on a.salesId=b.id `
        }
    }
    if(buName) {
        let contact = ` select id from contact where tenantId=${tid} and name like '%buName%' `
        sql += ` left join ${contact} c on a.buId=c.id `
    }
    // 拜访数
    let bfs = ` select salesId  from (${sql}) a group by salesId  ` // 有拜访的业务员
    let bfsywy = `select salesId, count(*) total from (${sql}) a group by salesId ` // 业务员的拜访数量
    let bfsywykh = `select salesId,count(*) total from (select salesId, buId from (${sql}) a group by salesId,buId) a group by salesId` // 业务员的拜访客户数量
    let salesAvg = `select salesId, avg(duration) total from (select  billDate,salesId,sum(cast(duration as int )) duration from (${sql}) a group by billDate,salesId) a group by  salesId `
    let salesAvgContact = `select salesId, avg(duration) total from (select  buId,salesId,sum(cast(duration as int )) duration from (${sql}) a group by buId,salesId) a group by salesId `
    let allc = `select salesId,count(*) total from contact where tenantId=${tid} and isDel=0 group by salesId`
    let allsql = ` select a.*,co.total contacts,b.total bfNo,c.total bfKhNo,round(isnull(d.total,0)*1.0/3600,2) avgDay,round(isnull(e.total,0)*1.0/60,2) avgContact from (${bfs}) a
                  inner join (${allc}) co on a.salesId=co.salesId
                  left join (${bfsywy}) b on a.salesId=b.salesId  left join (${bfsywykh}) c on a.salesId=c.salesId
                 left join (${salesAvg}) d on a.salesId=d.salesId left join (${salesAvgContact}) e on a.salesId=e.salesId `

    allsql = `select a.*,b.name salesName from (${allsql}) a inner join admin b on a.salesId=b.id `

    let pagesql = ` ${allsql} order by a.bfKhNo desc   `
    let list =  await db.query(pagesql, {type: QueryTypes.SELECT })
    //
    let pt = `select count(*) total from (${allsql}) a  `
    let ptotal = await db.query(pt, {type: QueryTypes.SELECT, plain: true})
    let sets = {}
    let gp = {}
    if(beg && end) {
        if(tool.dayjs(end).diff(beg)/1000/3600/24 < 7) {
            let sql = `select salesId,longitude,latitude,billDate,lastDistance from contact_bf where tenantId=${tid} and billDate between '${beg}' and '${end}' and state='已完成' order by id asc `
            let ret = await db.query(sql, {type: QueryTypes.SELECT})
            // 仓库坐标
            sets = await db.models['contactBfSets'].findOne({
                where: {
                    tenantId: tid
                },
                attributes: ['longitude', 'latitude']
            })
            gp = tool.xe.groupBy(ret, 'salesId')
        }
    }
    try {
        list.map(row => {
            row.unBfKhNo = (row.contacts?tool.xe.toNumber(row.contacts):0 ) - (row.bfKhNo?tool.xe.toNumber(row.bfKhNo):0)
            row.bfFgl =  tool.xe.round(tool.xe.divide(row.bfKhNo, row.contacts) * 100, 2)
            let sid = row.salesId.toString()
            let detail = tool.xe.groupBy(gp[sid], 'billDate')
            let dis_lh = 0 // 来回公里
            let dis_bf = 0 // 拜访总距离
            for (let i in detail) {
                if(detail[i].length > 0) {
                    let first = detail[i][0]
                    let last = detail[i][detail[i].length - 1]
                    if(sets.longitude && sets.latitude && first.longitude && first.latitude && last.longitude && last.latitude) {
                        let l = tool.getDistance(sets.longitude, sets.latitude, first.longitude, first.latitude)
                        let h = tool.getDistance(sets.longitude, sets.latitude, last.longitude, last.latitude)
                        dis_lh = tool.xe.add(dis_lh, l)
                        dis_lh = tool.xe.add(dis_lh, h)
                    }
                    detail[i].map(val => {
                        dis_bf = tool.xe.add(dis_bf, val.lastDistance)
                    })
                }
            }

            let lh = tool.xe.round(tool.xe.divide(dis_lh, 1000),2)
            let bf = tool.xe.round(tool.xe.divide(dis_bf, 1000),2)
            let all = tool.xe.round(tool.xe.divide(tool.xe.add(dis_lh, dis_bf), 1000),2)
            row.dis_lh = lh ? lh : ''
            row.dis_bf = bf ? bf : ''
            row.dis_all = all ? all : ''
        })
    }
    catch (e) {

    }
    let ret = {
        total:  ptotal.total,
        list: list,
        sets: sets
    }
    return ctx.suc('', ret)
})
// 今日任务完成情况
router.post('/report/todayBfTask', async ctx => {
    let tid = ctx.user.tenantId;
    let taskNo = 0;
    // 今日任务数量
    let taskNoModel = await db.models['contactBfSets'].findOne({
        where: {
            tenantId: tid
        }
    })
    if(taskNoModel) {
        taskNo = taskNoModel.taskNo
    }
    let today = tool.getDay()
    let sql = `
     select a.salesId,${taskNo} taskNo, a.bfNo,b.name salesName from (
         select salesId,count(*) bfNo from (select salesId,buId from contact_bf where tenantId=${tid} and bilLDate='${today}' and state='已完成' group by salesId, buId) a group by salesId
     ) a inner join admin b on a.salesId=b.id order by a.bfNo desc `
    let bfsModel = await db.query(sql, {type: QueryTypes.SELECT})
    bfsModel.map(row => {
        row.wcl = tool.xe.toNumber(row.taskNo) === 0 ? 0 : tool.xe.round( (row.bfNo*1.0 / row.taskNo)* 100, 2)
    })
    return ctx.suc('', bfsModel)

})
// 简报
router.post('/report/jb', async ctx => {

})
// 排行
router.post('/report/ph', async ctx => {

})
// 客户数
router.post('/report/todayKhs', async ctx => {
    let today = tool.getDay('')
    let ret = await biz.todayKhs(ctx, today)
    return ctx.suc('', ret)
})
// 排行实时
router.post('/report/todayPh', async ctx => {
    let today = tool.getDay('')
    let list = await biz.todayPh(ctx, today)
    return ctx.suc('', list)
})

// 正在拜访中的数据
router.post('/report/todayUndone', async ctx => {
    let today = tool.getDay('')
    let list = await biz.todayUndone(ctx, today)
    return ctx.suc('', list)
})
// 利润表明细
router.post('/report/profitDetail', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let tenantId = user.tenantId;
    let subjectCode = body.subjectCode;
    let field = body.field;
    let ym = body.ym;

    if (['zcMonthEnd', 'zcYearBegin', 'fzMonthEnd', 'fzYearBegin'].indexOf(field) !== -1) {
        let subjectName = body.subjectName;
        let list = await db.query(`exec get_asset_detail @tenantId=${tenantId},@ym=${ym},@field=${field},@subjectName=${subjectName}`, {type: QueryTypes.SELECT});
        return ctx.suc('', list);
    }
    else {
        let list = await db.query(`exec get_profit_detail @tenantId=${tenantId},@ym=${ym},@field=${field},@subjectCode=${subjectCode}`, {type: QueryTypes.SELECT});
        return ctx.suc('', list);
    }
})
// 进货报表
router.post('/report/ch_jh', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let tenantId = user.tenantId;
    let beginDate = body.beginDate;
    let endDate = body.endDate;
    let buId = body.buId ? body.buId : 0;
    let invId = body.invId ? body.invId : 0;
    if (!beginDate) {
        return ctx.err('请选择开始日期');
    }
    if (!endDate) {
        return ctx.err('请选择结束日期');
    }
    let bu = ''
    if(buId) bu = ` and a.buId=${buId} `
    let inv = ''
    if(invId) inv = ` and a.invId=${invId} `
    let bills = `
        with bills as
        (
                     select b.calNum,
                             b.qty,
                             b.amount,
                             b.invId,
                             b.iid,
                             a.transType,
                             a.buId,
                             c.name buName,
                             vg.name invName,
                             vg.spec,vg.unit1,vg.unit2
                      from invoice a
                          inner join v_main_info b on a.id = b.iid and a.tenantId=b.tenantId and a.billDate between '${beginDate}' and '${endDate}'
                      inner join contact c on a.buId=c.id and a.tenantId=c.tenantId
                      inner join v_goods vg on b.invId=vg.id and b.tenantId=vg.tenantId 
                      where a.tenantId = ${tenantId}
                        and a.billType = 'PUR'
                        and a.billState = 'accounted'
                        ${bu}
                        ${inv}
        ) 
        select * from bills 
    `
    let list = await db.query(bills,{type: QueryTypes.SELECT})
    let inBills = tool.xe.groupBy(list, 'iid')
    let inBills1 = tool._.groupBy(list, 'iid')
    let total = Object.keys(inBills).length
    let inAmount = tool.xe.toFixed(tool.xe.sum(list,'amount'), 2)
    list.map(it => {
        // it.inCass = tool.calAssQty(it,'inCass');
        // it.inBackCass = calAssQty(it,'inBackCass');
    })
    return ctx.suc('', {
        inBills: total,
        inAmount,
        // topInvName,
        // topBuName
    });
})
router.post('/report/ch_xs', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let tenantId = user.tenantId;
    let beginDate = body.beginDate;
    let endDate = body.endDate;
    let buId = body.buId ? body.buId : 0;
    let invId = body.invId ? body.invId : 0;
    if (!beginDate) {
        return ctx.err('请选择开始日期');
    }
    if (!endDate) {
        return ctx.err('请选择结束日期');
    }
    let bu = ''
    if(buId) bu = ` and a.buId=${buId} `
    let inv = ''
    if(invId) inv = ` and a.invId=${invId} `
    let bills = `
        with bills as
        (
                     select b.calNum,
                             b.qty,
                             b.amount,
                             b.invId,
                             b.iid,
                             a.transType,
                             a.buId,
                             c.name buName,
                             vg.name invName,
                             vg.spec,vg.unit1,vg.unit2
                      from invoice a
                          inner join v_main_info b on a.id = b.iid and a.tenantId=b.tenantId and a.billDate between '${beginDate}' and '${endDate}'
                      inner join contact c on a.buId=c.id and a.tenantId=c.tenantId
                      inner join v_goods vg on b.invId=vg.id and b.tenantId=vg.tenantId 
                      where a.tenantId = ${tenantId}
                        and a.billType = 'PUR'
                        and a.billState = 'accounted'
                        ${bu}
                        ${inv}
        ) 
        select * from bills 
    `
    let list = await db.query(bills,{type: QueryTypes.SELECT})
    let inBills = tool.xe.groupBy(list, 'iid')
    let inBills1 = tool._.groupBy(list, 'iid')
    let total = Object.keys(inBills).length
    let inAmount = tool.xe.toFixed(tool.xe.sum(list,'amount'), 2)
    list.map(it => {
        // it.inCass = tool.calAssQty(it,'inCass');
        // it.inBackCass = calAssQty(it,'inBackCass');
    })
    return ctx.suc('', {
        inBills: total,
        inAmount,
        // topInvName,
        // topBuName
    });
})

// 现金银行报表
router.post('/report/accounts', async ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let formData = body.formData
    let tenantId = user.tenantId;
    let beginDate = formData.beginDate ? formData.beginDate: '2001-01-01';
    let endDate = formData.endDate?formData.endDate:tool.getDay();
    let buId = formData.buId
    let accId = formData.accId
    let bills = `
       select isInit, tenantId,'' buId,'' buName, accId, accName, sr, zc, jy, billDate, billNo, transTypeName, buName, remark from
        (
            select 1 isInit, a.tenantId,0 buId,a.id accId,a.name accName,isnull(sr,0) sr,isnull(zc,0) zc,isNull(jy,0) jy,'' billDate,'' billNo,'期初' transTypeName,'' buName,'' remark from v_account a left join
            (
                select tenantId,accId,accName,sum(sr) sr,sum(zc) zc,(sum(sr)-sum(zc)) jy from v_account_detail_accounted where tenantId=${tenantId} and billDate<'${beginDate}'  group by tenantId, accId, accName
            ) b on a.tenantId=b.tenantId and a.id=b.accId
            union all 
            select 0 isInit, tenantId,buId,accId,accName,sr,zc,(sr-zc) jy,billDate,billNo,transTypeName,buName,remark from 
            v_account_detail_accounted where tenantId=${tenantId} and billDate>='${beginDate}' and billDate<='${endDate}' 
       ) a where tenantId= ${tenantId} 
    `
    if(buId) bills += ` and buId=${buId} `
    if(accId) bills += ` and accId=${accId} `
    bills += ` order by accId,billDate `
    let list = await db.query(bills,{type: QueryTypes.SELECT})
    let ret = []
    for (let i = 0; i < list.length; i++) {
        let row = list[i]
       if(i > 0) {
           if(tool.xe.toNumber(row.accId) !== tool.xe.toNumber(list[i-1].accId)) {
               ret.push({
                   accId: list[i-1].accId,
                   transTypeName: '小计',
                   sr: 0,
                   zc: 0,
                   jy: 0
               })
           }
       }
        ret.push(row)
    }
    if(ret.length > 0) {
        ret.push({
            accId: ret[ret.length-1].accId,
            transTypeName: '小计',
            sr: 0,
            zc: 0,
            jy: 0
        })
        for (let i = 0; i < ret.length; i++) {
            if(i > 0) {
                if(ret[i].transTypeName !== '期初')
                    ret[i].jy = tool.add(ret[i].jy, ret[i-1].jy)
            }
        }
    }

    return ctx.suc('', {
        total: 0,
        list: ret
    });
})



// 销售利润表

module.exports = router;